package com.zz.bsea.web.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class StudentService {
	
	public List<Student> getAllStudent(String sql){
		List<Student> list=new ArrayList();
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();
			ResultSet  rs=st.executeQuery(sql);
			while(rs.next()) {
				Student student=new Student();
				student.setName(rs.getString("name"));
				student.setId(rs.getInt("id"));
				student.setScore(rs.getInt("score"));
				student.setSex(rs.getString("sex"));
				list.add(student);
			}
			
			JDBCBasic.closeConn(st, rs, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	
	public Student add(Student student) {
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();
			
			Random r=new Random();
			int id=r.nextInt(1000);
			student.setId(id);
			String sql="insert into student (id,sex,score,name) values ("+id+",'"+student.getSex()+"',"+student.getScore()+",'"+student.getName()+"')";
			st.execute(sql);
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return student;
	}
	
	
	public boolean updateNameById(String name,int id) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();
			
			
			String sql="update student set name='"+name+"' where id="+id;
			if(st.executeUpdate(sql)>0) {
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return flag;
	}
	
	
	public boolean deleteByName(String name) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();
			String sql="delete from student where name='"+name+"'";
			if(st.executeUpdate(sql)>0) {
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
}
